package com.apes.scm.account.account.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.account.account.model.InventoryConsign;
import com.apes.scm.masterdata.party.model.PartyRole;
import com.apes.scm.masterdata.product.model.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-03-14 13:35
 */
public interface InventoryConsignRepository extends CustomRepository<InventoryConsign, Long> {

    @Query("select e from InventoryConsign e where e.company = :company and e.customer = :customer and e.product = :product and e.balanceQty - e.frostBalanceQty > 0 and e.priceUnit = :priceUnit order by e.batchNumber asc, e.batchSerial asc")
    List<InventoryConsign> findInventoryConsignForOrder(@Param("company") PartyRole company, @Param("customer") PartyRole customer, @Param("product") Product product, @Param("priceUnit") double priceUnit);

    @Query("select e from InventoryConsign e where e.company = :company and e.customer = :customer and e.product = :product and e.consumeQty - e.frostConsumeQty > 0 and e.priceUnit = :priceUnit order by e.batchNumber desc, e.batchSerial desc")
    List<InventoryConsign> findInventoryConsignForReturn(@Param("company") PartyRole company, @Param("customer") PartyRole customer, @Param("product") Product product, @Param("priceUnit") double priceUnit);

    @Query(
            value = " select new map(t.id as inventoryConsignId, t.product.id as productId, k.name as productName, l.id as taxId, l.name as taxName, " +
                    "                g.id as uomId, g.name as uomName, t.priceUnit as priceUnit, t.balanceQty - t.frostBalanceQty as pendingQty) " +
                    " from InventoryConsign t left join t.uom g left join t.product k left join t.tax l left join t.company left join t.customer " +
                    " where t.balanceQty - t.frostBalanceQty > 0 and  t.company.id = :company and t.customer.id = :customerId order by t.product.id",
            countQuery = "select count(*) from InventoryConsign t where t.balanceQty - t.frostBalanceQty > 0 and t.company.id = :company and t.customer.id = :customerId"
    )
    Page<Map<String,Object>> findInventoryConsignForConsume(@Param("company") String companyId,@Param("customer") String customerId, Pageable pageable);


    @Query(
            value = " select new map(t.id as inventoryConsignId, t.product.id as productId, k.name as productName, l.id as taxId, l.name as taxName, " +
                    "                g.id as uomId, g.name as uomName, t.priceUnit as priceUnit, t.balanceQty - t.frostBalanceQty as pendingQty) " +
                    " from InventoryConsign t left join t.uom g left join t.product k left join t.tax l left join t.company left join t.customer " +
                    " where t.balanceQty - t.frostBalanceQty > 0 and  t.InDate >= :startDate and t.InDate <= :endDate and t.company.id = :companyId and t.customer.id = :customerId order by t.product.id",
            countQuery = "select count(*) from InventoryConsign t where t.balanceQty - t.frostBalanceQty > 0 and t.InDate >= :startDate and t.InDate <= :endDate and t.company.id = :companyId and t.customer.id = :customerId"
    )
    Page<Map<String,Object>> findInventoryConsignForConsume(@Param("companyId") String companyId,@Param("customerId") String customerId, @Param("startDate") Date startDate, @Param("endDate") Date endDate, Pageable pageable);

    @Query(
            value = " select new map(t.id as inventoryConsignId, t.product.id as productId, k.name as productName, l.id as taxId, l.name as taxName, " +
                    "                g.id as uomId, g.name as uomName, t.priceUnit as priceUnit, t.consumeQty - t.frostConsumeQty as pendingQty) " +
                    " from InventoryConsign t left join t.uom g left join t.product k left join t.tax l left join t.company left join t.customer " +
                    " where t.consumeQty - t.frostConsumeQty > 0 and  t.company.id = :companyId and t.customer.id = :customerId order by t.product.id",
            countQuery = "select count(*) from InventoryConsign t where t.consumeQty - t.frostConsumeQty > 0 and t.company.id = :companyId and t.customer.id = :customerId"
    )
    Page<Map<String,Object>> findInventoryConsignForReturn(@Param("companyId") String companyId, @Param("customerId") String customerId, Pageable pageable);

    @Query(
            value = " select new map(t.id as inventoryConsignId, t.product.id as productId, k.name as productName, l.id as taxId, l.name as taxName, " +
                    "                g.id as uomId, g.name as uomName, t.priceUnit as priceUnit, t.consumeQty - t.frostConsumeQty as pendingQty) " +
                    " from InventoryConsign t left join t.uom g left join t.product k left join t.tax l left join t.company left join t.customer " +
                    " where t.consumeQty - t.frostConsumeQty > 0 and t.InDate >= :startDate and t.InDate <= :endDate and t.company.id = :companyId and t.customer.id = :customerId order by t.product.id",
            countQuery = "select count(*) from InventoryConsign t where t.consumeQty - t.frostConsumeQty > 0 and t.InDate >= :startDate and t.InDate <= :endDate and t.company.id = :companyId and t.customer.id = :customerId"
    )
    Page<Map<String,Object>> findInventoryConsignForReturn(@Param("companyId") String companyId, @Param("customerId") String customerId,@Param("startDate") Date startDate, @Param("endDate") Date endDate, Pageable pageable);


    @Query(value = "SELECT nvl(max(BATCH_SERIAL),0)+1 AS serial FROM acc_inventory_consign t WHERE t.BATCH_NUMBER = ?1", nativeQuery = true)
    Integer findMaxBatchSerial(Long batchNumber);
}
